﻿/*  Eleven Business Suite : Un ERP básico para pequeños empresarios.
 *  Copyright (C) 2011  Francis Bolaños Arias
 *
 *  This program is free software: you can redistribute it and/or modify
 *  it under the terms of the GNU General Public License as published by
 *  the Free Software Foundation, either version 2 of the License, or
 *  (at your option) any later version.
 *
 *  This program is distributed in the hope that it will be useful,
 *  but WITHOUT ANY WARRANTY; without even the implied warranty of
 *  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 *  GNU General Public License for more details.
 *
 *  You should have received a copy of the GNU General Public License
 *  along with this program.  If not, see <http://www.gnu.org/licenses/>;.
 */

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using ElevenGlobal;

namespace ElevenData
{
    public class Consultar
    {

        #region Gerencia

        public static DataTable ConsultarPaises()
        {
            DataTable dt = new DataTable();
            SqlConnection conn = new SqlConnection(Configuracion.connectionString);
            SqlCommand comando = new SqlCommand("SELECT * FROM [11PAISES] ORDER BY Nombre");
            SqlDataAdapter adapter = new SqlDataAdapter();
            adapter.SelectCommand = comando;
            comando.Connection = conn;
            conn.Open();
            adapter.Fill(dt);
            conn.Close();
            return dt;
        }

        public static DataTable ConsultarProvincias(int IdPais)
        {
            DataTable dt = new DataTable();
            SqlConnection conn = new SqlConnection(Configuracion.connectionString);
            SqlCommand comando = new SqlCommand("SELECT * FROM [11PROVINCIAS] WHERE IdPais = @IdPais ORDER BY Nombre");
            SqlParameter param;
            param = comando.Parameters.Add("@IdPais", SqlDbType.Int);
            param.Value = IdPais;
            SqlDataAdapter adapter = new SqlDataAdapter();
            adapter.SelectCommand = comando;
            comando.Connection = conn;
            conn.Open();
            adapter.Fill(dt);
            conn.Close();
            return dt;
        }

        public static DataTable ConsultarCantones(int IdProvincia)
        {
            DataTable dt = new DataTable();
            SqlConnection conn = new SqlConnection(Configuracion.connectionString);
            SqlCommand comando = new SqlCommand("SELECT * FROM [11CANTONES] WHERE IdProvincia=@IdProvincia ORDER BY Nombre");
            SqlParameter param;
            param = comando.Parameters.Add("@IdProvincia", SqlDbType.Int);
            param.Value = IdProvincia;
            SqlDataAdapter adapter = new SqlDataAdapter();
            adapter.SelectCommand = comando;
            comando.Connection = conn;
            conn.Open();
            adapter.Fill(dt);
            conn.Close();
            return dt;
        }

        public static DataTable ConsultarDistritos(int IdCanton)
        {
            DataTable dt = new DataTable();
            SqlConnection conn = new SqlConnection(Configuracion.connectionString);
            SqlCommand comando = new SqlCommand("SELECT * FROM [11DISTRITOS] WHERE IdCanton=@IdCanton ORDER BY Nombre");
            SqlParameter param;
            param = comando.Parameters.Add("@IdCanton", SqlDbType.Int);
            param.Value = IdCanton;
            SqlDataAdapter adapter = new SqlDataAdapter();
            adapter.SelectCommand = comando;
            comando.Connection = conn;
            conn.Open();
            adapter.Fill(dt);
            conn.Close();
            return dt;
        }

        #endregion

        #region Inventario

        public static DataTable ConsultarProveedores()
        {
            DataTable dt = new DataTable();
            SqlConnection conn = new SqlConnection(Configuracion.connectionString);
            SqlCommand comando = new SqlCommand("SELECT * FROM [11VSTPROVEEDORXREGION] ORDER BY Nombre");
            SqlDataAdapter adapter = new SqlDataAdapter();
            adapter.SelectCommand = comando;
            comando.Connection = conn;
            conn.Open();
            adapter.Fill(dt);
            conn.Close();
            return dt;
        }

        public static DataTable ConsultarProveedoresXDistrito(int IdDistrito)
        {
            DataTable dt = new DataTable();
            SqlConnection conn = new SqlConnection(Configuracion.connectionString);
            SqlCommand comando = new SqlCommand("SELECT * FROM [11VSTPROVEEDORXREGION] WHERE IdDistrito = @IdDistrito");
            SqlParameter param;
            param = comando.Parameters.Add("@IdDistrito", SqlDbType.Int);
            param.Value = IdDistrito;
            SqlDataAdapter adapter = new SqlDataAdapter();
            adapter.SelectCommand = comando;
            comando.Connection = conn;
            conn.Open();
            adapter.Fill(dt);
            conn.Close();
            return dt;
        }

        public static DataTable ConsultarCategorias()
        {
            DataTable dt = new DataTable();
            SqlConnection conn = new SqlConnection(Configuracion.connectionString);
            SqlCommand comando = new SqlCommand("SELECT * FROM [11PRODCAT] ORDER BY Nombre");
            SqlDataAdapter adapter = new SqlDataAdapter();
            adapter.SelectCommand = comando;
            comando.Connection = conn;
            conn.Open();
            adapter.Fill(dt);
            conn.Close();
            return dt;
        }

        public static DataTable ConsultarSubcategorias(int IdCategoria)
        {
            DataTable dt = new DataTable();
            SqlConnection conn = new SqlConnection(Configuracion.connectionString);
            SqlCommand comando = new SqlCommand("SELECT * FROM [11PRODSUBCAT] WHERE IdCategoria = @IdCategoria ORDER BY Nombre");
            SqlParameter param;
            param = comando.Parameters.Add("@IdCategoria", SqlDbType.Int);
            param.Value = IdCategoria;
            SqlDataAdapter adapter = new SqlDataAdapter();
            adapter.SelectCommand = comando;
            comando.Connection = conn;
            conn.Open();
            adapter.Fill(dt);
            conn.Close();
            return dt;
        }

        public static DataTable ConsultarProductosXProveedores(int IdProveedor)
        {
            DataTable dt = new DataTable();
            SqlConnection conn = new SqlConnection(Configuracion.connectionString);
            SqlCommand comando = new SqlCommand("SELECT * FROM [11VSTPRODUCTO] WHERE IdProveedor = @IdProveedor ORDER BY Nombre");
            SqlParameter param;
            param = comando.Parameters.Add("@IdProveedor", SqlDbType.Int);
            param.Value = IdProveedor;
            SqlDataAdapter adapter = new SqlDataAdapter();
            adapter.SelectCommand = comando;
            comando.Connection = conn;
            conn.Open();
            adapter.Fill(dt);
            conn.Close();
            return dt;
        }

        public static DataTable ConsultarProductosXSubcategoria(int IdSubcategoria)
        {
            DataTable dt = new DataTable();
            SqlConnection conn = new SqlConnection(Configuracion.connectionString);
            SqlCommand comando = new SqlCommand("SELECT * FROM [11VSTPRODUCTO] WHERE IdSubcategoria = @IdSubcategoria ORDER BY Nombre");
            SqlParameter param;
            param = comando.Parameters.Add("@IdSubcategoria", SqlDbType.Int);
            param.Value = IdSubcategoria;
            SqlDataAdapter adapter = new SqlDataAdapter();
            adapter.SelectCommand = comando;
            comando.Connection = conn;
            conn.Open();
            adapter.Fill(dt);
            conn.Close();
            return dt;
        }

        public static DataTable ConsultarOrdenCompraXProveedor(int IdProveedor)
        {
            DataTable dt = new DataTable();
            SqlConnection conn = new SqlConnection(Configuracion.connectionString);
            SqlCommand comando = new SqlCommand("SELECT * FROM [11VSTORDENCOMPRA] WHERE IdProveedor = @IdProveedor ORDER BY Fecha DESC");
            SqlParameter param;
            param = comando.Parameters.Add("@IdProveedor", SqlDbType.Int);
            param.Value = IdProveedor;
            SqlDataAdapter adapter = new SqlDataAdapter();
            adapter.SelectCommand = comando;
            comando.Connection = conn;
            conn.Open();
            adapter.Fill(dt);
            conn.Close();
            return dt;
        }

        public static DataTable ConsultarOrdenCompraXFecha(DateTime Fecha)
        {
            DataTable dt = new DataTable();
            SqlConnection conn = new SqlConnection(Configuracion.connectionString);
            SqlCommand comando = new SqlCommand("SELECT * FROM [11VSTORDENCOMPRA] WHERE Fecha BETWEEN @Fecha1 AND @Fecha2 ORDER BY Fecha DESC");
            SqlParameter param;
            param = comando.Parameters.Add("@Fecha1", SqlDbType.DateTime);
            param.Value = new DateTime(Fecha.Year, Fecha.Month, Fecha.Day, 0, 0, 0);
            param = comando.Parameters.Add("@Fecha2", SqlDbType.DateTime);
            param.Value = new DateTime(Fecha.Year, Fecha.Month, Fecha.Day, 23, 59, 59);
            SqlDataAdapter adapter = new SqlDataAdapter();
            adapter.SelectCommand = comando;
            comando.Connection = conn;
            conn.Open();
            adapter.Fill(dt);
            conn.Close();
            return dt;
        }

        public static DataTable ConsultarOrdenCompraXID(int IdOrdenCompra)
        {
            DataTable dt = new DataTable();
            SqlConnection conn = new SqlConnection(Configuracion.connectionString);
            SqlCommand comando = new SqlCommand("SELECT * FROM [11VSTORDENCOMPRA] WHERE IdOrdenCompra = @IdOrdenCompra");
            SqlParameter param;
            param = comando.Parameters.Add("@IdOrdenCompra", SqlDbType.Int);
            param.Value = IdOrdenCompra;
            SqlDataAdapter adapter = new SqlDataAdapter();
            adapter.SelectCommand = comando;
            comando.Connection = conn;
            conn.Open();
            adapter.Fill(dt);
            conn.Close();
            return dt;
        }

        public static DataTable ConsultarDTOrdenCompra(int IdOrdenCompra)
        {
            DataTable dt = new DataTable();
            SqlConnection conn = new SqlConnection(Configuracion.connectionString);
            SqlCommand comando = new SqlCommand("SELECT * FROM [11VSTDTORDENCOMPRA] WHERE IdOrdenCompra = @IdOrdenCompra");
            SqlParameter param;
            param = comando.Parameters.Add("@IdOrdenCompra", SqlDbType.Int);
            param.Value = IdOrdenCompra;
            SqlDataAdapter adapter = new SqlDataAdapter();
            adapter.SelectCommand = comando;
            comando.Connection = conn;
            conn.Open();
            adapter.Fill(dt);
            conn.Close();
            return dt;
        }

        public static DataTable ConsultarDTOrdenCompraXProducto(int IdProducto)
        {
            DataTable dt = new DataTable();
            SqlConnection conn = new SqlConnection(Configuracion.connectionString);
            SqlCommand comando = new SqlCommand("SELECT * FROM [11VSTDTORDENCOMPRA] WHERE IdProducto = @IdProducto");
            SqlParameter param;
            param = comando.Parameters.Add("@IdProducto", SqlDbType.Int);
            param.Value = IdProducto;
            SqlDataAdapter adapter = new SqlDataAdapter();
            adapter.SelectCommand = comando;
            comando.Connection = conn;
            conn.Open();
            adapter.Fill(dt);
            conn.Close();
            return dt;
        }

        public static DataTable ConsultarDTOrdenCompraPrecioCosto(int IdProducto)
        {
            DataTable dt = new DataTable();
            SqlConnection conn = new SqlConnection(Configuracion.connectionString);
            SqlCommand comando = new SqlCommand("SELECT * FROM [11VSTPRECIOCOSTO] WHERE IdProducto = @IdProducto ORDER BY Fecha DESC");
            SqlParameter param;
            param = comando.Parameters.Add("@IdProducto", SqlDbType.Int);
            param.Value = IdProducto;
            SqlDataAdapter adapter = new SqlDataAdapter();
            adapter.SelectCommand = comando;
            comando.Connection = conn;
            conn.Open();
            adapter.Fill(dt);
            conn.Close();
            return dt;
        }

        public static DataTable ConsultarProductoXID(int IdProducto, SqlConnection conn, SqlTransaction tran)
        {
            DataTable dt = new DataTable();
            SqlCommand comando = new SqlCommand("SELECT * FROM [11PRODUCTOS] WHERE IdProducto = @IdProducto");
            SqlParameter param;
            param = comando.Parameters.Add("@IdProducto", SqlDbType.Int);
            param.Value = IdProducto;
            SqlDataAdapter adapter = new SqlDataAdapter();
            adapter.SelectCommand = comando;
            comando.Connection = conn;
            comando.Transaction = tran;
            adapter.Fill(dt);
            return dt;
        }

        public static DataTable ConsultarOrdenCompraRecienInsertada(SqlConnection conn, SqlTransaction tran, string Guid)
        {
            //Reemplazar por una consulta más estricta, donde todo sea absolutamente igual a lo insertado
            //ya que la concurrencia al ingresar facturas al mismo tiempo podría corromper los datos.
            DataTable dt = new DataTable();
            SqlCommand comando = new SqlCommand("SELECT IdOrdenCompra FROM [11ORDENCOMPRA] WHERE Guid = @Guid");
            SqlParameter param;
            param = comando.Parameters.Add("@Guid", SqlDbType.VarChar, 50);
            param.Value = Guid;
            SqlDataAdapter adapter = new SqlDataAdapter();
            adapter.SelectCommand = comando;
            comando.Connection = conn;
            comando.Transaction = tran;
            adapter.Fill(dt);
            return dt;
        }

        public static DataTable ConsultarPreciosVenta(int IdProducto)
        {
            DataTable dt = new DataTable();
            SqlConnection conn = new SqlConnection(Configuracion.connectionString);
            SqlCommand comando = new SqlCommand("SELECT * FROM [11VSTPRECIOVENTA] WHERE IdProducto = @IdProducto ORDER BY Fecha DESC");
            SqlParameter param;
            param = comando.Parameters.Add("@IdProducto", SqlDbType.Int);
            param.Value = IdProducto;
            SqlDataAdapter adapter = new SqlDataAdapter();
            adapter.SelectCommand = comando;
            comando.Connection = conn;
            conn.Open();
            adapter.Fill(dt);
            conn.Close();
            return dt;
        }

        public static DataTable ConsultarInventario(int Inicio, int Final, string Ordenar, bool Ascendente)
        {
            DataTable dt = new DataTable();
            SqlConnection conn = new SqlConnection(Configuracion.connectionString);
            string Descendente = "";
            if (!Ascendente) {
                Descendente = " DESC";
            }
            SqlCommand comando = new SqlCommand("WITH RESPUESTA AS ("
                + "SELECT *, "
                + "ROW_NUMBER () OVER (ORDER BY " + Ordenar + Descendente + ") as ORDENAR "
                + "FROM [11VSTPREINVENTARIO]) "
                + "SELECT * FROM RESPUESTA "
                + "WHERE ORDENAR BETWEEN @Inicio AND @Final");
            SqlParameter param;
            param = comando.Parameters.Add("@Inicio", SqlDbType.Int);
            param.Value = Inicio;
            param = comando.Parameters.Add("@Final", SqlDbType.Int);
            param.Value = Final;
            param = comando.Parameters.Add("@Ordenar", SqlDbType.VarChar, 20);
            param.Value = Ordenar;
            SqlDataAdapter adapter = new SqlDataAdapter();
            adapter.SelectCommand = comando;
            comando.Connection = conn;
            conn.Open();
            adapter.Fill(dt);
            conn.Close();
            return dt;
        }

        public static DataTable ConsultarInventarioMes(int Mes, int Anno, int Inicio, int Final, string Ordenar, bool Ascendente)
        {
            DataTable dt = new DataTable();
            SqlConnection conn = new SqlConnection(Configuracion.connectionString);
            string Descendente = "";
            if (!Ascendente)
            {
                Descendente = " DESC";
            }
            SqlCommand comando = new SqlCommand("WITH RESPUESTA AS ("
                + "SELECT *, "
                + "ROW_NUMBER () OVER (ORDER BY " + Ordenar + Descendente + ") as ORDENAR "
                + "FROM (SELECT * FROM [11FUNPREINVENTARIOMES] (@Mes, @Anno)) AS FUNCI) "
                + "SELECT * FROM RESPUESTA "
                + "WHERE ORDENAR BETWEEN @Inicio AND @Final");
            SqlParameter param;
            param = comando.Parameters.Add("@Inicio", SqlDbType.Int);
            param.Value = Inicio;
            param = comando.Parameters.Add("@Final", SqlDbType.Int);
            param.Value = Final;
            param = comando.Parameters.Add("@Mes", SqlDbType.Int);
            param.Value = Mes;
            param = comando.Parameters.Add("@Anno", SqlDbType.Int);
            param.Value = Anno;
            param = comando.Parameters.Add("@Ordenar", SqlDbType.VarChar, 20);
            param.Value = Ordenar;
            SqlDataAdapter adapter = new SqlDataAdapter();
            adapter.SelectCommand = comando;
            comando.Connection = conn;
            conn.Open();
            adapter.Fill(dt);
            conn.Close();
            return dt;
        }

        public static DataTable ConsultarPerdidas(DateTime Fecha)
        {
            DataTable dt = new DataTable();
            SqlConnection conn = new SqlConnection(Configuracion.connectionString);
            SqlCommand comando = new SqlCommand("SELECT * FROM [11VSTPERDIDAS] WHERE Fecha BETWEEN @Fecha1 AND @Fecha2 ORDER BY Fecha DESC");
            SqlParameter param;
            param = comando.Parameters.Add("@Fecha1", SqlDbType.DateTime);
            param.Value = new DateTime(Fecha.Year, Fecha.Month, Fecha.Day, 0, 0, 0);
            param = comando.Parameters.Add("@Fecha2", SqlDbType.DateTime);
            param.Value = new DateTime(Fecha.Year, Fecha.Month, Fecha.Day, 23, 59, 59);
            SqlDataAdapter adapter = new SqlDataAdapter();
            adapter.SelectCommand = comando;
            comando.Connection = conn;
            conn.Open();
            adapter.Fill(dt);
            conn.Close();
            return dt;
        }

        public static DataTable ConsultarPrecioCostoMAX(int IdProducto)
        {
            DataTable dt = new DataTable();
            SqlConnection conn = new SqlConnection(Configuracion.connectionString);
            SqlCommand comando = new SqlCommand("SELECT * FROM [11VSTINVENPRECIOCOSTO] WHERE IdProducto = @IdProducto");
            SqlParameter param;
            param = comando.Parameters.Add("@IdProducto", SqlDbType.Int);
            param.Value = IdProducto;
            SqlDataAdapter adapter = new SqlDataAdapter();
            adapter.SelectCommand = comando;
            comando.Connection = conn;
            conn.Open();
            adapter.Fill(dt);
            conn.Close();
            return dt;
        }

        public static DataTable ConsultarProductosLike(string Columna, string Consulta)
        {
            DataTable dt = new DataTable();
            SqlConnection conn = new SqlConnection(Configuracion.connectionString);
            SqlCommand comando = new SqlCommand("SELECT * FROM [11VSTPRODUCTO] WHERE " + Columna + " LIKE '%' + @Consulta + '%' ORDER BY Nombre");
            SqlDataAdapter adapter = new SqlDataAdapter();
            SqlParameter param;
            param = comando.Parameters.Add("@Consulta", SqlDbType.VarChar, 255);
            param.Value = Consulta;
            adapter.SelectCommand = comando;
            comando.Connection = conn;
            conn.Open();
            adapter.Fill(dt);
            conn.Close();
            return dt;
        }

        public static DataTable ConsultarProveedoresLike(string Columna, string Consulta)
        {
            DataTable dt = new DataTable();
            SqlConnection conn = new SqlConnection(Configuracion.connectionString);
            SqlCommand comando = new SqlCommand("SELECT * FROM [11VSTPROVEEDORXREGION] WHERE " + Columna + " LIKE '%' + @Consulta + '%' ORDER BY Nombre");
            SqlDataAdapter adapter = new SqlDataAdapter();
            SqlParameter param;
            param = comando.Parameters.Add("@Consulta", SqlDbType.VarChar, 255);
            param.Value = Consulta;
            adapter.SelectCommand = comando;
            comando.Connection = conn;
            conn.Open();
            adapter.Fill(dt);
            conn.Close();
            return dt;
        }

        #endregion

        #region Punto de Venta

        public static DataTable ConsultarClientes()
        {
            DataTable dt = new DataTable();
            SqlConnection conn = new SqlConnection(Configuracion.connectionString);
            SqlCommand comando = new SqlCommand("SELECT * FROM [11VSTCLIENTEXREGION] ORDER BY NombreCompleto");
            SqlDataAdapter adapter = new SqlDataAdapter();
            adapter.SelectCommand = comando;
            comando.Connection = conn;
            conn.Open();
            adapter.Fill(dt);
            conn.Close();
            return dt;
        }

        public static DataTable ConsultarClienteXID(int IdFactura)
        {
            DataTable dt = new DataTable();
            SqlConnection conn = new SqlConnection(Configuracion.connectionString);
            SqlCommand comando = new SqlCommand("SELECT * FROM [11VSTCLIENTEXREGION] WHERE IdCliente = @IdCliente");
            SqlParameter param;
            param = comando.Parameters.Add("@IdCliente", SqlDbType.Int);
            param.Value = IdFactura;
            SqlDataAdapter adapter = new SqlDataAdapter();
            adapter.SelectCommand = comando;
            comando.Connection = conn;
            conn.Open();
            adapter.Fill(dt);
            conn.Close();
            return dt;
        }

        public static DataTable validarFacturaCliente(int IdCliente)
        {
            DataTable dt = new DataTable();
            SqlConnection conn = new SqlConnection(Configuracion.connectionString);
            SqlCommand comando = new SqlCommand("SELECT TOP 1 * FROM [11FACTURA] WHERE IdCliente = @IdCliente");
            SqlParameter param;
            param = comando.Parameters.Add("@IdCliente", SqlDbType.Int);
            param.Value = IdCliente;
            SqlDataAdapter adapter = new SqlDataAdapter();
            adapter.SelectCommand = comando;
            comando.Connection = conn;
            conn.Open();
            adapter.Fill(dt);
            conn.Close();
            return dt;
        }

        public static DataTable ConsultarFacturaXFecha(DateTime Fecha)
        {
            DataTable dt = new DataTable();
            SqlConnection conn = new SqlConnection(Configuracion.connectionString);
            SqlCommand comando = new SqlCommand("SELECT * FROM [11FACTURA] WHERE Fecha BETWEEN @Fecha1 AND @Fecha2 ORDER BY Fecha DESC");
            SqlParameter param;
            param = comando.Parameters.Add("@Fecha1", SqlDbType.DateTime);
            param.Value = new DateTime(Fecha.Year, Fecha.Month, Fecha.Day, 0, 0, 0);
            param = comando.Parameters.Add("@Fecha2", SqlDbType.DateTime);
            param.Value = new DateTime(Fecha.Year, Fecha.Month, Fecha.Day, 23, 59, 59);
            SqlDataAdapter adapter = new SqlDataAdapter();
            adapter.SelectCommand = comando;
            comando.Connection = conn;
            conn.Open();
            adapter.Fill(dt);
            conn.Close();
            return dt;
        }

        public static DataTable ConsultarFacturaRecienInsertada(SqlConnection conn, SqlTransaction tran, string Guid)
        {
            DataTable dt = new DataTable();
            SqlCommand comando = new SqlCommand("SELECT IdFactura FROM [11FACTURA] WHERE Guid = @Guid");
            SqlParameter param;
            param = comando.Parameters.Add("@Guid", SqlDbType.VarChar, 50);
            param.Value = Guid;
            SqlDataAdapter adapter = new SqlDataAdapter();
            adapter.SelectCommand = comando;
            comando.Connection = conn;
            comando.Transaction = tran;
            adapter.Fill(dt);
            return dt;
        }

        public static DataTable ConsultarFacturaXID(int IdFactura)
        {
            DataTable dt = new DataTable();
            SqlConnection conn = new SqlConnection(Configuracion.connectionString);
            SqlCommand comando = new SqlCommand("SELECT * FROM [11FACTURA] WHERE IdFactura = @IdFactura");
            SqlParameter param;
            param = comando.Parameters.Add("@IdFactura", SqlDbType.Int);
            param.Value = IdFactura;
            SqlDataAdapter adapter = new SqlDataAdapter();
            adapter.SelectCommand = comando;
            comando.Connection = conn;
            conn.Open();
            adapter.Fill(dt);
            conn.Close();
            return dt;
        }

        public static DataTable ConsultarDTFactura(int IdFactura)
        {
            DataTable dt = new DataTable();
            SqlConnection conn = new SqlConnection(Configuracion.connectionString);
            SqlCommand comando = new SqlCommand("SELECT * FROM [11VSTDTFACTURA] WHERE IdFactura = @IdFactura");
            SqlParameter param;
            param = comando.Parameters.Add("@IdFactura", SqlDbType.Int);
            param.Value = IdFactura;
            SqlDataAdapter adapter = new SqlDataAdapter();
            adapter.SelectCommand = comando;
            comando.Connection = conn;
            conn.Open();
            adapter.Fill(dt);
            conn.Close();
            return dt;
        }

        public static DataTable ConsultarProductoXIDNoTran(int IdProducto)
        {
            DataTable dt = new DataTable();
            SqlConnection conn = new SqlConnection(Configuracion.connectionString);
            SqlCommand comando = new SqlCommand("SELECT * FROM [11PRODUCTOS] WHERE IdProducto = @IdProducto");
            SqlParameter param;
            param = comando.Parameters.Add("@IdProducto", SqlDbType.Int);
            param.Value = IdProducto;
            SqlDataAdapter adapter = new SqlDataAdapter();
            adapter.SelectCommand = comando;
            comando.Connection = conn;
            conn.Open();
            adapter.Fill(dt);
            conn.Close();
            return dt;
        }

        public static DataTable ConsultarProductoXCodBarras(string IdCodigoBarras)
        {
            DataTable dt = new DataTable();
            SqlConnection conn = new SqlConnection(Configuracion.connectionString);
            SqlCommand comando = new SqlCommand("SELECT TOP 1 * FROM [11PRODUCTOS] WHERE IdCodigoBarras = @IdCodigoBarras");
            SqlParameter param;
            param = comando.Parameters.Add("@IdCodigoBarras", SqlDbType.VarChar, 50);
            param.Value = IdCodigoBarras;
            SqlDataAdapter adapter = new SqlDataAdapter();
            adapter.SelectCommand = comando;
            comando.Connection = conn;
            conn.Open();
            adapter.Fill(dt);
            conn.Close();
            return dt;
        }

        public static DataTable ConsultarPrecioVentaMAX(int IdProducto)
        {
            DataTable dt = new DataTable();
            SqlConnection conn = new SqlConnection(Configuracion.connectionString);
            SqlCommand comando = new SqlCommand("SELECT * FROM [11VSTPREINVENTARIO] WHERE IdProducto = @IdProducto");
            SqlParameter param;
            param = comando.Parameters.Add("@IdProducto", SqlDbType.Int);
            param.Value = IdProducto;
            SqlDataAdapter adapter = new SqlDataAdapter();
            adapter.SelectCommand = comando;
            comando.Connection = conn;
            conn.Open();
            adapter.Fill(dt);
            conn.Close();
            return dt;
        }

        public static DataTable ConsultarClientesAlfa(string Columna, string Letra)
        {
            DataTable dt = new DataTable();
            if (Columna.Contains("1º"))
            {
                Columna = Columna.Replace("1º ", "") + "1";
            }
            if (Columna.Contains("2º"))
            {
                Columna = Columna.Replace("2º ", "") + "2";
            }
            SqlConnection conn = new SqlConnection(Configuracion.connectionString);
            if ((Letra.Length == 1) && ((Columna.Length == 9) || (Columna.Length == 6)))
            {
                SqlCommand comando = new SqlCommand("SELECT * FROM [11VSTCLIENTEXREGION] WHERE " + Columna + " LIKE '" + Letra + "%' ORDER BY NombreCompleto");
                SqlDataAdapter adapter = new SqlDataAdapter();
                adapter.SelectCommand = comando;
                comando.Connection = conn;
                conn.Open();
                adapter.Fill(dt);
                conn.Close();
                return dt;
            }
            else
            {
                return null;
            }
        }

        public static DataTable ConsultarClientesLike(string Columna, string Consulta)
        {
            DataTable dt = new DataTable();
            SqlConnection conn = new SqlConnection(Configuracion.connectionString);
            SqlCommand comando = new SqlCommand("SELECT * FROM [11VSTCLIENTEXREGION] WHERE " + Columna + " LIKE '%' + @Consulta + '%' ORDER BY NombreCompleto");
            SqlDataAdapter adapter = new SqlDataAdapter();
            SqlParameter param;
            param = comando.Parameters.Add("@Consulta", SqlDbType.VarChar, 255);
            param.Value = Consulta;
            adapter.SelectCommand = comando;
            comando.Connection = conn;
            conn.Open();
            adapter.Fill(dt);
            conn.Close();
            return dt;
        }

        public static DataTable ConsultarDatosEmpresa()
        {
            DataTable dt = new DataTable();
            SqlConnection conn = new SqlConnection(Configuracion.connectionString);
            SqlCommand comando = new SqlCommand("SELECT * FROM [11EMPRESA] WHERE IdEmpresa = 1");
            SqlDataAdapter adapter = new SqlDataAdapter();
            adapter.SelectCommand = comando;
            comando.Connection = conn;
            conn.Open();
            adapter.Fill(dt);
            conn.Close();
            return dt;
        }

        #endregion

        #region Clientes

        public static DataTable ConsultarConsultas(DateTime Fecha)
        {
            DataTable dt = new DataTable();
            SqlConnection conn = new SqlConnection(Configuracion.connectionString);
            SqlCommand comando = new SqlCommand("SELECT * FROM [11CONSULTAS] WHERE Fecha BETWEEN @Fecha1 AND @Fecha2 ORDER BY Fecha DESC");
            SqlParameter param;
            param = comando.Parameters.Add("@Fecha1", SqlDbType.DateTime);
            param.Value = new DateTime(Fecha.Year, Fecha.Month, Fecha.Day, 0, 0, 0);
            param = comando.Parameters.Add("@Fecha2", SqlDbType.DateTime);
            param.Value = new DateTime(Fecha.Year, Fecha.Month, Fecha.Day, 23, 59, 59);
            SqlDataAdapter adapter = new SqlDataAdapter();
            adapter.SelectCommand = comando;
            comando.Connection = conn;
            conn.Open();
            adapter.Fill(dt);
            conn.Close();
            return dt;
        }

        #endregion

    }
}
